package net.hekka.canyoutalk.data.store;

import net.hekka.canyoutalk.share.constant.DatabaseType;

/**
 * 임시 로그 테이블("temp_log_table") 조회 관련 SQL문들을 관리하는 클래스.
 * @author Jeffrey Ha
 */
public class TempLogQuerySqlStore {

    /**
     * StatusChangeList 데이터를 조회한다.
     * @param _databaseType 데이터베이스 타입
     * @return StatusChangeList 데이터를 조회한다.
     */
    public static String getStatusChangeListSelectSql(String _databaseType) {

        StringBuilder sql = new StringBuilder();

        if (_databaseType.equals(DatabaseType.MYSQL)) {
            sql.append("\n select y.mynumber,    ");
            sql.append("\n        ifnull(y.myname,'unknown') as myname,    ");
            sql.append("\n        y.yournumber,  ");
            sql.append("\n        ifnull(y.yourname,'') as yourname,  ");
            sql.append("\n        y.seq,         ");
            sql.append("\n        y.status,      ");
            sql.append("\n        y.check_num,   ");
            sql.append("\n        y.packet_data, ");
            sql.append("\n        y.busy_min,    ");
            sql.append("\n        y.write_time,  ");
            sql.append("\n        y.link_seq     ");
            sql.append("\n   from (select mynumber, max(seq)as maxseq     ");
            sql.append("\n           from temp_log_table  ");
            sql.append("\n          where substr(packet_data,1,3) = 'CHG' ");
            sql.append("\n          group by mynumber     ");
            sql.append("\n        ) x,  ");
            sql.append("\n        (select a.*, b.name myname, c.name yourname  ");
            sql.append("\n           from temp_log_table a left join phonebook b on ( a.mynumber = b.number )    ");
            sql.append("\n                                 left join phonebook c on ( a.yournumber = c.number )  ");
            sql.append("\n        ) y   ");
            sql.append("\n  where x.maxseq = y.seq  ");
            sql.append("\n  order by x.maxseq desc  ");
            sql.append("\n  limit ?, ?              ");
        }
        return sql.toString();
    }

    /**
     * BroadcastList 데이터를 조회한다.
     * @param _databaseType 데이터베이스 타입
     * @return BroadcastList 데이터를 조회한다.
     */
    public static String getBroadcastListSelectSql(String _databaseType) {

        StringBuilder sql = new StringBuilder();

        if (_databaseType.equals(DatabaseType.MYSQL)) {
            sql.append("\n  select a.mynumber,     ");
            sql.append("\n         ifnull(b.name,'unknown') as myname,  ");
            sql.append("\n         a.yournumber,   ");
            sql.append("\n         ifnull(c.name,'') as yourname,       ");
            sql.append("\n         a.seq,          ");
            sql.append("\n         a.status,       ");
            sql.append("\n         a.check_num,    ");
            sql.append("\n         a.packet_data,  ");
            sql.append("\n         a.busy_min,     ");
            sql.append("\n         a.write_time,   ");
            sql.append("\n         a.link_seq      ");
            sql.append("\n    from temp_log_table a left join phonebook b on ( a.mynumber = b.number )    ");
            sql.append("\n                          left join phonebook c on ( a.yournumber = c.number )  ");
            sql.append("\n   where 1=1             ");
            sql.append("\n     and a.link_seq = ?  ");
            sql.append("\n   order by a.seq desc   ");
        }
        return sql.toString();
    }

    /**
     * 내 친구들의 최근 상태를 조회하기 위한 SQL문을 구한다.
     * @param _databaseType 데이터베이스 타입
     * @return 내 친구들의 최근 상태를 조회하기 위한 SQL문
     */
    public static String getFriendStateListSelectSql(String _databaseType) {

        StringBuilder sql = new StringBuilder();

        if (_databaseType.equals(DatabaseType.MYSQL)) {
            sql.append("\n  select x.seq, ");
            sql.append("\n         x.yournumber, ");
            sql.append("\n        x.yourname, ");
            sql.append("\n         x.mynumber, ");
            sql.append("\n         x.myname, ");
            sql.append("\n         x.status, ");
            sql.append("\n         x.busy_min, ");
            sql.append("\n         x.write_time, ");
            sql.append("\n         x.packet_data, ");
            sql.append("\n         x.link_seq, ");
            sql.append("\n         x.check_num ");
            sql.append("\n  from ");
            sql.append("\n  ( ");
            sql.append("\n  select d.*, ");
            sql.append("\n         ifnull(b.name,'unknown') as myname, ");
            sql.append("\n         ifnull(c.name,'') as yourname ");
            sql.append("\n    from temp_log_table d  left join phonebook b on ( d.mynumber = b.number ) ");
            sql.append("\n                           left join phonebook c on ( d.yournumber = c.number ) ");
            sql.append("\n  ) x, ");
            sql.append("\n  ( ");
            sql.append("\n  select max(a.seq) as max_seq, ");
            sql.append("\n         a.mynumber, ");
            sql.append("\n         a.yournumber, ");
            sql.append("\n         b.friend ");
            sql.append("\n    from temp_log_table a, numbertable b ");
            sql.append("\n   where a.yournumber = ( select mynumber from temp_log_table where seq = ? ) ");
            sql.append("\n     and b.owner  = a.yournumber ");
            sql.append("\n     and b.friend = a.mynumber ");
            sql.append("\n   group by mynumber ");
            sql.append("\n   ) y ");
            sql.append("\n   where x.seq = y.max_seq ");
            sql.append("\n     and x.link_seq <> ? ");

        }
        return sql.toString();
    }

    /**
     * 내 모든 친구들의 최근 상태를 조회하기 위한 SQL문을 구한다.
     * @param _databaseType 데이터베이스 타입
     * @return 내 모든 친구들의 최근 상태를 조회하기 위한 SQL문
     */
    public static String getMyAllFriendStateListSelectSql(String _databaseType) {

        StringBuilder sql = new StringBuilder();

        if (_databaseType.equals(DatabaseType.MYSQL)) {
            sql.append("\n  select x.seq, ");
            sql.append("\n         x.yournumber, ");
            sql.append("\n        x.yourname, ");
            sql.append("\n         x.mynumber, ");
            sql.append("\n         x.myname, ");
            sql.append("\n         x.status, ");
            sql.append("\n         x.busy_min, ");
            sql.append("\n         x.write_time, ");
            sql.append("\n         x.packet_data, ");
            sql.append("\n         x.link_seq, ");
            sql.append("\n         x.check_num ");
            sql.append("\n  from ");
            sql.append("\n  ( ");
            sql.append("\n  select d.*, ");
            sql.append("\n         ifnull(b.name,'unknown') as myname, ");
            sql.append("\n         ifnull(c.name,'') as yourname ");
            sql.append("\n    from temp_log_table d  left join phonebook b on ( d.mynumber = b.number ) ");
            sql.append("\n                           left join phonebook c on ( d.yournumber = c.number ) ");
            sql.append("\n  ) x, ");
            sql.append("\n  ( ");
            sql.append("\n  select max(a.seq) as max_seq, ");
            sql.append("\n         a.mynumber, ");
            sql.append("\n         a.yournumber, ");
            sql.append("\n         b.friend ");
            sql.append("\n    from temp_log_table a, numbertable b ");
            sql.append("\n   where a.yournumber = ( select mynumber from temp_log_table where seq = ? ) ");
            sql.append("\n     and b.owner  = a.yournumber ");
            sql.append("\n     and b.friend = a.mynumber ");
            sql.append("\n   group by mynumber ");
            sql.append("\n   ) y ");
            sql.append("\n   where x.seq = y.max_seq ");
        }
        return sql.toString();
    }

    /**
     * Normal View 데이터를 조회하기 위한 SQL문을 구한다.
     * @param _databaseType 데이터베이스 타입
     * @return Normal View 데이터를 조회하기 위한 SQL문
     */
    public static String getNormalViewSelectSql(String _databaseType) {

        StringBuilder sql = new StringBuilder();

        if (_databaseType.equals(DatabaseType.MYSQL)) {
            sql.append("\n  select a.mynumber,     ");
            sql.append("\n         ifnull(b.name,'unknown') as myname,  ");
            sql.append("\n         a.yournumber,   ");
            sql.append("\n         ifnull(c.name,'') as yourname,       ");
            sql.append("\n         a.seq,          ");
            sql.append("\n         a.status,       ");
            sql.append("\n         a.check_num,    ");
            sql.append("\n         a.packet_data,  ");
            sql.append("\n         a.busy_min,     ");
            sql.append("\n         a.write_time,   ");
            sql.append("\n         a.link_seq      ");
            sql.append("\n    from temp_log_table a left join phonebook b on ( a.mynumber = b.number )    ");
            sql.append("\n                          left join phonebook c on ( a.yournumber = c.number )  ");
            sql.append("\n   order by a.seq desc   ");
            sql.append("\n   limit ?, ?            ");
        }
        return sql.toString();
    }

    /**
     * Normal View 데이터수를 조회하기 위한 SQL문을 구한다.
     * @param _databaseType 데이터베이스 타입
     * @return Normal View 데이터수를 조회하기 위한 SQL문
     */
    public static String getNormalViewTotalCountSelectSql(String _databaseType) {

        StringBuilder sql = new StringBuilder();

        if (_databaseType.equals(DatabaseType.MYSQL)) {
            sql.append("\n  select count(*) count   ");
            sql.append("\n    from temp_log_table   ");
        }
        return sql.toString();
    }

    /**
     * Detail View 데이터를 조회한다.
     * @param _databaseType 데이터베이스 타입
     * @return Detail View 데이터를 조회한다.
     */
    public static String getDetailViewSelectSql(String _databaseType) {

        StringBuilder sql = new StringBuilder();

        if (_databaseType.equals(DatabaseType.MYSQL)) {
            sql.append("\n  select x.rownum,      ");
            sql.append("\n         x.mynumber,    ");
            sql.append("\n         x.myname,      ");
            sql.append("\n         x.yournumber,  ");
            sql.append("\n         x.yourname,    ");
            sql.append("\n         x.seq,         ");
            sql.append("\n         x.status,      ");
            sql.append("\n         x.check_num,   ");
            sql.append("\n         x.packet_data, ");
            sql.append("\n         x.busy_min,    ");
            sql.append("\n         x.write_time,  ");
            sql.append("\n         x.link_seq     ");
            sql.append("\n    from ( select @rnum:=@rnum+1 as rownum  ");
            sql.append("\n                , a.mynumber                ");
            sql.append("\n                , ifnull(b.name,'unknown') as myname  ");
            sql.append("\n                , a.yournumber              ");
            sql.append("\n                , ifnull(c.name,'') as yourname       ");
            sql.append("\n                , a.seq          ");
            sql.append("\n                , a.status       ");
            sql.append("\n                , a.check_num    ");
            sql.append("\n                , a.packet_data  ");
            sql.append("\n                , a.busy_min     ");
            sql.append("\n                , a.write_time   ");
            sql.append("\n                , a.link_seq     ");
            sql.append("\n             from (select @rnum:=0) r,      ");
            sql.append("\n                  temp_log_table a left join phonebook b on ( a.mynumber = b.number )    ");
            sql.append("\n                                   left join phonebook c on ( a.yournumber = c.number )  ");
            sql.append("\n            where substr(a.packet_data,1,3) = 'CHG'  ");
            sql.append("\n            order by a.seq  ");
            sql.append("\n         ) x   ");
            sql.append("\n   where 1=1   ");
            sql.append("\n   order by x.rownum desc   ");
            sql.append("\n   limit ?, ?  ");
        }
        return sql.toString();
    }

    /**
     * Detail View 데이터수를 조회하기 위한 SQL문을 구한다.
     * @param _databaseType 데이터베이스 타입
     * @return Detail View 데이터수를 조회하기 위한 SQL문
     */
    public static String getDetailViewTotalCountSelectSql(String _databaseType) {
        StringBuilder sql = new StringBuilder();

        if (_databaseType.equals(DatabaseType.MYSQL)) {
            sql.append("\n  select count(*) count   ");
            sql.append("\n    from temp_log_table   ");
            sql.append("\n   where substr(packet_data,1,3) = 'CHG'   ");
        }
        return sql.toString();
    }

    /**
     * StatusChange 데이터수를 조회하기 위한 SQL문을 구한다.
     * @param _databaseType 데이터베이스 타입
     * @return StatusChange 데이터수를 조회하기 위한 SQL문
     */
    public static String getStatusChangeTotalCountSelectSql(String _databaseType) {
        StringBuilder sql = new StringBuilder();

        if (_databaseType.equals(DatabaseType.MYSQL)) {
            sql.append("\n select count(x.mynumber) count  ");
            sql.append("\n   from (select mynumber, max(seq)as maxseq      ");
            sql.append("\n           from temp_log_table   ");
            sql.append("\n          where substr(packet_data,1,3) = 'CHG'  ");
            sql.append("\n          group by mynumber      ");
            sql.append("\n        ) x  ");
        }
        return sql.toString();
    }

    /**
     * 선택한 myNumber의 데이터 중 Sequence의 최대값을 조회하기 위한 SQL문을 구한다.
     * @param _databaseType 데이터베이스 타입
     * @return 선택한 myNumber의 데이터 중 Sequence의 최대값을 조회하기 위한 SQL문
     */
    public static String getMaxSequenceOfMyNumberSelectSql(String _databaseType) {
        StringBuilder sql = new StringBuilder();

        if (_databaseType.equals(DatabaseType.MYSQL)) {
            sql.append("\n select max(seq) as maxSequence  ");
            sql.append("\n   from temp_log_table      ");
            sql.append("\n  where mynumber = ?        ");
            sql.append("\n    and substr(packet_data,1,3) = 'CHG' ");
        }
        return sql.toString();
    }

}
